﻿using AmesaExporter.Common;
using AmesaExporter.Products;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Objects.DataClasses;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AmesaExporter
{
    public partial class Form1 : Form
    {
        Model.NopAsorTestEntities _dataContext;
        private ConnectionManager.ConnectionManagerHelper MyConnectionManager;

        public Form1()
        {
            InitializeComponent();
        }

        #region Events
        private void Form1_Load(object sender, EventArgs e)
        {
            _dataContext = new Model.NopAsorTestEntities();
            label1.Text = string.Format("Ver.{0}", Assembly.GetEntryAssembly().GetName().Version.ToString());

            progress.Value = 0;
        }

        private void exportProductsButton_Click(object sender, EventArgs e)
        {
            ImportProducts();
        }

        private void exportCustomerButton_Click(object sender, EventArgs e)
        {
            ImportCustomers();
        }
        #endregion

        #region Private Methods

        private void ImportProducts()
        {
            OpenFileDialog dialog = new OpenFileDialog();

            lblProceso.Text = "Procesando";

            if (dialog.ShowDialog() != DialogResult.OK) return;

            MyConnectionManager = new ConnectionManager.ConnectionManagerHelper(dialog.FileName);

            SetProgressBar("select Count(*) from [Products$] where sku is not null and Precio is not null");

            AssignValuesToProducts(MyConnectionManager.GetReader("select * from [Products$] where sku is not null and Precio is not null"), Common.SchemaObj.CreatesProductsSchema());

            lblProceso.Text = "Proceso finalizado";
            _dataContext.SaveChanges();
        }

        private void SetProgressBar(string CommandToProgress)
        {
            progress.Maximum = MyConnectionManager.GetScalar(CommandToProgress);
            progress.Value = 0;
            progress.Minimum = 0;
        }

        private void AssignValuesToProducts(OleDbDataReader dr, List<SchemaObj> columnExcel)
        {
            while (dr.Read())
            {

                List<string> assignedColumns = new List<string>();
                Model.Product _Product = new Model.Product();

                progress.Value += 1;// (100 / progress.Maximum);

                for (int i = 0; i < dr.FieldCount; i++)
                {
                    // El Enum contiene la columna.
                    bool exist = columnExcel.Exists(f => f.excel == dr.GetName(i));

                    //Si la columna existe en el Enum.
                    if (exist)
                    {
                        try
                        {
                            //Asigna el valor en el objeto.
                            var value = dr[i];
                            AssignValueObject(_Product, dr, i, columnExcel);

                            //Agrega la columna a la lista de columnas asignadas.
                            assignedColumns.Add(dr.GetName(i));
                        }
                        catch (Exception)
                        {
                        }
                    }
                }
                //Llena con valores por defecto los campos que no tienen columna en el excel.
                AssignDefaultValue(_Product, assignedColumns, columnExcel);

                if (string.IsNullOrEmpty(_Product.Name) || string.IsNullOrEmpty(_Product.Sku))
                    continue;

                if (_dataContext.Products.Any(f => f.Name == _Product.Name && f.Sku == _Product.Sku && !f.Deleted))
                {
                    var product = _dataContext.Products.First(f => f.Name == _Product.Name && f.Sku == _Product.Sku);
                    product.Name = _Product.Name;
                    product.ShortDescription = _Product.ShortDescription;
                    product.FullDescription = _Product.FullDescription;
                    product.StockQuantity = _Product.StockQuantity;
                    product.Sku = _Product.Sku;
                    product.Price = _Product.Price;
                    product.MinStockQuantity = _Product.MinStockQuantity;
                    _dataContext.SaveChanges();
                }
                else
                    _dataContext.Products.Add(_Product);
            }
        }

        private void AssignDefaultValue(Model.Product p, List<string> assignedColumns, List<SchemaObj> columnExcel)
        {
            DefaultProductValues dv = new DefaultProductValues();
            var columnsAssigned = columnExcel.Where(f => assignedColumns.Contains(f.excel)).ToList();
            var type = p.GetType();
            var properties = type.GetProperties();
            var propertiesNoAssign = properties.Where(f => !columnsAssigned.Select(g => g.objeto).Contains(f.Name)).ToList();

            foreach (var item in propertiesNoAssign)
                if (dv.GetType().GetProperty(item.Name) != null)
                {
                    var value = dv.GetType().GetProperty(item.Name).GetValue(dv, null);

                    //Asigna el valor por defecto a la propiedad.
                    item.SetValue(p, value, null);
                }
        }

        private void AssignValueObject(Model.Product p, OleDbDataReader dr, int index, List<SchemaObj> columnExcel)
        {
            var type = p.GetType();
            var properties = type.GetProperties();

            foreach (var property in properties)
            {
                var col = columnExcel.FirstOrDefault(f => f.excel == dr.GetName(index));
                if (col != null && property.Name == col.objeto)
                    property.SetValue(p, Convert.ChangeType(dr[index], property.PropertyType), null);
            }
        }

        private void ImportCustomers()
        {
            lblProceso.Text = "Procesando";

            OpenFileDialog dialog = new OpenFileDialog();
            if (dialog.ShowDialog() != DialogResult.OK) return;

            MyConnectionManager = new ConnectionManager.ConnectionManagerHelper(dialog.FileName);

            SetProgressBar("select Count(*) from [Customers$]");

            AssignValueCustomer(MyConnectionManager.GetReader("select * from [Customers$]"), Common.SchemaObj.CreatesCustomersSchema());

            lblProceso.Text = "Proceso finalizado";
        }

        private void AssignValueCustomer(OleDbDataReader dr, List<SchemaObj> columnExcel)
        {
            // DataTable schema = dr.GetSchemaTable();

            while (dr.Read())
            {
                List<string> assignedColumns = new List<string>();
                Model.Customer _Customer = new Model.Customer();

                progress.Value += 1;

                for (int i = 0; i < dr.FieldCount; i++)
                {
                    // El Enum contiene la columna.
                    bool exist = columnExcel.Exists(f => f.excel == dr.GetName(i));

                    //Si la columna existe en el Enum.
                    if (exist)
                    {
                        try
                        {
                            //Asigna el valor en el objeto.
                            //var value = dr[i];
                            AssignValueObjectCustomer(_Customer, dr, i, columnExcel);
                            //Agrega la columna a la lista de columnas asignadas.
                            assignedColumns.Add(dr.GetName(i));
                        }
                        catch (Exception)
                        {
                        }
                    }
                }
                //Llena con valores por defecto los campos que no tienen columna en el excel.
                AssignDefaultCustomerValue(_Customer, assignedColumns, columnExcel);
                
                _Customer.Username = _Customer.Email;

                //Si el usuario Existe
                if (_dataContext.Customer.FirstOrDefault(f => f.Username == _Customer.Username && !f.Deleted) != null)
                {
                    var customerModified = _dataContext.Customer.First(f => f.Username == _Customer.Username && !f.Deleted);
                    customerModified.Username = _Customer.Email;
                    customerModified.Email = _Customer.Email;
                    customerModified.Credit = _Customer.Credit;

                    var gaFirstName = _dataContext.GenericAttribute.FirstOrDefault(f => f.EntityId == customerModified.Id
                                                                                && f.KeyGroup == "Customer"
                                                                                && f.Key == "FirstName");
                    gaFirstName.Value = (string)dr["Nombre"];

                    var gaLastName = _dataContext.GenericAttribute.FirstOrDefault(f => f.EntityId == customerModified.Id
                                                                               && f.KeyGroup == "Customer"
                                                                               && f.Key == "LastName");
                    gaLastName.Value = (string)dr["Apellido"];

                    //Direccion
                    if (customerModified.Address2.Count() > 0)
                    {
                        var addressCustomer = customerModified.Address2.First();
                        addressCustomer.FirstName = gaFirstName.Value;
                        addressCustomer.LastName = gaLastName.Value;
                        addressCustomer.Email = _Customer.Email;
                        addressCustomer.City = Convert.ToString(dr["Localidad"]);
                        addressCustomer.Address2 = Convert.ToString(dr["Dirección"]);
                        addressCustomer.PhoneNumber = Convert.ToString(dr["Teléfono"]);
                    }
                    else
                    {
                        Model.Address addressCustomer = new Model.Address();
                        addressCustomer.FirstName = gaFirstName.Value;
                        addressCustomer.LastName = gaLastName.Value;
                        addressCustomer.Email = _Customer.Email;
                        addressCustomer.City = Convert.ToString(dr["Localidad"]);
                        addressCustomer.Address2 = Convert.ToString(dr["Dirección"]);
                        addressCustomer.PhoneNumber = Convert.ToString(dr["Teléfono"]);
                        _Customer.Address2.Add(addressCustomer);
                    }

                    _dataContext.SaveChanges();
                }
                else
                {
                    _dataContext.Customer.Add(_Customer);
                    _dataContext.SaveChanges();

                    //Crea el rol guess al usuario.
                    Model.Customer_CustomerRole_Mapping ccm = new Model.Customer_CustomerRole_Mapping();
                    ccm.CustomerRole_Id = 4;//Guess
                    ccm.Customer_Id = _Customer.Id;

                    Model.GenericAttribute gaFirstName = new Model.GenericAttribute();
                    Model.GenericAttribute gaLastName = new Model.GenericAttribute();
                    Model.Address addressCustomer = new Model.Address();

                    //Crea los atributos. Nombre y Apellido
                    gaFirstName.EntityId = _Customer.Id;
                    gaFirstName.KeyGroup = "Customer";
                    gaFirstName.Key = "FirstName";
                    gaFirstName.Value = (string)dr["Nombre"];

                    gaLastName.EntityId = _Customer.Id;
                    gaLastName.KeyGroup = "Customer";
                    gaLastName.Key = "LastName";
                    gaLastName.Value = (string)dr["Apellido"];

                    //Crea la Direccion al usuario.
                    addressCustomer.FirstName = gaFirstName.Value;
                    addressCustomer.LastName = gaLastName.Value;
                    addressCustomer.Email = _Customer.Email;
                    addressCustomer.City = (string)dr["Localidad"];
                    addressCustomer.Address1 = (string)dr["Dirección"];
                    addressCustomer.PhoneNumber = Convert.ToString(dr["Teléfono"]);
                    addressCustomer.CreatedOnUtc = DateTime.Now;
                    _dataContext.Address.Add(addressCustomer);
                    //_dataContext.Address.Add(addressCustomer);
                    _dataContext.SaveChanges();

                    _Customer.Address2.Add(addressCustomer);
                    //c.a.BillingAddress_Id = addressCustomer.Id;

                    _dataContext.GenericAttribute.Add(gaFirstName);
                    _dataContext.GenericAttribute.Add(gaLastName);
                    _dataContext.Customer_CustomerRole_Mapping.Add(ccm);

                    _dataContext.SaveChanges();
                }
            }
        }

        private void AssignValueObjectCustomer(Model.Customer c, OleDbDataReader dr, int index, List<SchemaObj> columnExcel)
        {
            var type = c.GetType();
            var properties = type.GetProperties();

            foreach (var property in properties)
            {
                var col = columnExcel.FirstOrDefault(f => f.excel == dr.GetName(index));
                if (col != null && property.Name == col.objeto)
                    property.SetValue(c, Convert.ChangeType(dr[index], property.GetGetMethod().ReturnType), null);
            }
        }

        private void AssignDefaultCustomerValue(Model.Customer c, List<string> assignedColumns, List<SchemaObj> columnExcel)
        {
            Customers.DefaultCustomerValues dv = new Customers.DefaultCustomerValues();
            var columnsAssigned = columnExcel.Where(f => assignedColumns.Contains(f.excel)).ToList();
            var type = c.GetType();
            var properties = type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance)
                                 .Where(f => f.PropertyType != typeof(Model.Address)).ToList();

            var propertiesNoAssign = properties.Where(f => !columnsAssigned.Select(g => g.objeto).Contains(f.Name)).ToList();

            foreach (var item in propertiesNoAssign)
            {
                if (dv.GetType().GetProperty(item.Name) != null)
                {
                    var value = dv.GetType().GetProperty(item.Name).GetValue(dv, null);
                    var ff = Convert.ChangeType(value, dv.GetType().GetProperty(item.Name).PropertyType);
                    //Asigna el valor por defecto a la propiedad.
                    item.SetValue(c, value, null);
                }
            }
        }
        #endregion


    }
}
